package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Interesting;

public class InterestingDao {
	private int pageSize=5;
	/**
	 * 添加来源
	 */
	public boolean addInteresting(String name) throws SQLException {
		String sql="insert into interesting (interesting_name) values ( ? )";
		return Jdbc.getInstace().executeUpdate(sql, name);
	}
	
	/**
	 * 根据ID删除来源
	 */
	public boolean deleteInterestingById(String id) throws SQLException {
		String sql="delete from interesting where interesting_id=?";
		return Jdbc.getInstace().executeUpdate(sql, id);
	}
	
	/**
	 * 搜索总页数
	 */
	public int getTotalPage( String positionName) throws SQLException {
		String sql = "select count(*) total from interesting ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, positionName);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}
	/**
	 * 带条件分页查询
	 * @param type
	 * @param page
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public List<Interesting> getPageInterestingListByType(int page, String interestingName)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from interesting ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, interestingName);
		sql += " order by interesting_id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryUserList(sql, params.toArray());
	}
	/**
	 * 获取所有兴趣
	 * @throws SQLException 
	 */
	public List<Interesting> getAllInteresting() throws SQLException {
		String sql="select * from interesting";
		List<Map<String,Object>>list=Jdbc.getInstace().executeQueryForList(sql);
		return changelist(list);
	}
	/**
	 * 转换List
	 * @param list
	 * @return
	 */
	private List<Interesting> changelist(List<Map<String,Object>>list){
		List<Interesting>listInteresting=null;
		if(list!=null&&list.size()>0){
			//Interesting interesting=new Interesting();
			listInteresting=new ArrayList<>();
			for (Map<String, Object> map : list) {
				listInteresting.add(new Interesting(
						Integer.parseInt(map.get("interesting_id").toString()),
						(String)map.get("interesting_name")));
			}
		}
		return listInteresting;
		
	}
	
	private String buildSqlAndParams(List<Object> params, String sql,String interestingName) {
		//params.add(status);
		if (!StringUtils.isNullOrEmpty(interestingName)) {
			sql += "where interesting_name like ? ";
			params.add("%" + interestingName + "%");
		}

		return sql;
	}
	
	private List<Interesting> queryUserList(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<Interesting> interestingList = null;
		if (listData != null && listData.size() > 0) {
			interestingList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				interestingList.add(buildPosition(data));
			}
		}
		return interestingList;
	}
	
	private Interesting buildPosition(Map<String, Object> data) {
		Interesting interesting = new Interesting();
		interesting.setInterestingName((String)data.get("interesting_name"));
		if (data.get("interesting_id") != null) {
			interesting.setInterestingId(Integer.parseInt(data.get("interesting_id").toString()) );
		}
		return interesting;
	}
}
